import os
import shutil
from collections import defaultdict
from datetime import datetime
from urllib.parse import quote
import pandas as pd

import requests
from flask import request, jsonify, send_from_directory, url_for, send_file
import logging
from flask_restx import Namespace, Resource, fields
from flask_jwt_extended import jwt_required
from sqlalchemy import and_, or_
from werkzeug.security import safe_join
from werkzeug.utils import secure_filename

import app1
from app1.models import Zbfwb_kms_alarm

# 导入 tb_alarm 模型
from app1 import db
from app1 import api

from app1.customdate import CustomDate
from app1.utils import format_response

# 获取上传路径
UPLOAD_FOLDER = app1.app.config['UPLOAD_FOLDER']
UPLOAD_COPY_FOLDER = app1.app.config['UPLOAD_COPY_FOLDER']
DOWNLOAD_FOLDER = app1.app.config['DOWNLOAD_FOLDER']
BASE_RPT_DIR = app1.app.config['BASE_RPT_DIR']

# 确保上传和复制的目录存在
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
os.makedirs(UPLOAD_COPY_FOLDER, exist_ok=True)

# 创建 Flask-RESTx 命名空间
ns_alarm_rpt = Namespace('ns_alarm_rpt', description='告警汇总处理')

# # 定义文件上传的模型
# alarm_rpt_model = ns_alarm_rpt.model(
#     'alarm_rpt',
#     {
#         'file_name': fields.String(description='文件名', required=True, default="请替换文件名"),
#     }
# )

# 定义文件上传的模型
alarm_rpt_model = ns_alarm_rpt.model(
    'alarm_rpt',
    {
        'file': fields.Raw(required=True, description='文件上传')
    }
)

# 定义 Flask-RESTx 模型
alarm_rpt_return_model = ns_alarm_rpt.model('alarm_rpt_return', {
    'original_file_path': fields.String(description='上传文件目录'),
    'copied_file_path': fields.String(description='处理后的文件目录'),
    'download_url': fields.String(description='下载文件api的url')
})

# 定义外层的响应模型
response_alarm_rpt_model = ns_alarm_rpt.model('Response_alarm_rpt', {
    'message': fields.Nested(ns_alarm_rpt.model('Message_alarm_rpt', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'data': fields.List(fields.Nested(alarm_rpt_return_model))
    }))
})

# 定义需要读取文件列表的目录
alarm_file_list_query_model = ns_alarm_rpt.model(
    'alarm_file_list_query',
    {
        'dir_name': fields.String(description='指定目录', required=True, default="./app1/download"),
    }
)

# 定义 Flask-RESTx 模型
alarm_file_list_model = ns_alarm_rpt.model('alarm_file_list', {
    'file_name': fields.String(description='文件名称'),
    'file_ctime': fields.String(description='文件修改日期'),
    'file_type': fields.String(description='文件类型'),
    'file_size': fields.String(description='文件大小')
})

# 定义外层的响应模型
response_alarm_file_list_model = ns_alarm_rpt.model('Response_alarm_file_list', {
    'message': fields.Nested(ns_alarm_rpt.model('Message_alarm_file_list', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(alarm_file_list_model))
    }))
})

# 定义需要处理的文件和所在目录
alarm_file_fill_process_model = ns_alarm_rpt.model(
    'alarm_file_fill_process',
    {
        'dir_name': fields.String(description='指定目录', required=True, default="./app1/download"),
        'file_name': fields.String(description='指定文件', required=True, default=""),
        # 'sheet_name': fields.String(description='sheet名字', required=True, default=""),
    }
)

# 定义 Flask-RESTx 模型
alarm_file_fill_model = ns_alarm_rpt.model('alarm_file_fill', {
    'file_name': fields.String(description='文件名称'),
    'file_ctime': fields.String(description='文件修改日期'),
    'file_type': fields.String(description='文件类型'),
    'file_size': fields.String(description='文件大小')
})

# 定义外层的响应模型
response_alarm_file_fill_model = ns_alarm_rpt.model('Response_alarm_file_fill', {
    'message': fields.Nested(ns_alarm_rpt.model('Message_alarm_file_fill', {
        "result": fields.Integer(description='查询结果返回值'),
        "result_text": fields.String(description='查询结果信息'),
        'count': fields.Integer(description='查询结果返回数据条数'),
        'data': fields.List(fields.Nested(alarm_file_fill_model))
    }))
})

# # 使用 Flask-RESTx
# Excel_alarm_model = ns_alarm_rpt.model(
#     'excel_alarm',
#     {
#         "team": fields.String(description='团队'),
#         "sys_name": fields.String(description='系统名称'),
#         "dev_addr": fields.String(description='系统地址'),
#         "level": fields.String(description='告警级别'),
#         "occurrence_time": CustomDate(dt_format='str_time', description='首次发生时间'),
#         # "occurrence_time": fields.String(description='首次发生时间'),
#         "info": fields.String(description='告警内容'),
#         "occurrence_date": fields.String(description='日期'),
#         "cause": fields.String(description='告警主要原因'),
#         "link_warning": fields.String(description='是否变更关联告警'),
#         "link_change": fields.String(description='关联变更单号'),
#         "link_error": fields.String(description='是否故障关联告警'),
#     }
# )

# 使用 Flask-RESTx
Excel_alarm_model = ns_alarm_rpt.model(
    'excel_alarm',
    {
        "团队": fields.String(description='团队'),
        "系统名称": fields.String(description='系统名称'),
        "系统地址": fields.String(description='系统地址'),
        "告警级别": fields.String(description='告警级别'),
        "首次发生时间": CustomDate(dt_format='str_time', description='首次发生时间'),
        # "occurrence_time": fields.String(description='首次发生时间'),
        "告警内容": fields.String(description='告警内容'),
        "日期": fields.String(description='日期'),
        "告警主要原因": fields.String(description='告警主要原因'),
        "是否变更关联告警": fields.String(description='是否变更关联告警'),
        "关联变更单号": fields.String(description='关联变更单号'),
        "是否故障关联告警": fields.String(description='是否故障关联告警'),
    }
)


# 文件上传 API
@ns_alarm_rpt.route('/upload')
class AlarmRptUploadResource(Resource):
    @ns_alarm_rpt.expect(alarm_rpt_model)
    @ns_alarm_rpt.marshal_with(response_alarm_rpt_model)
    def post(self):
        try:
            logging.info("开始处理文件上传...")
            # # debug
            # filedata = api.payload
            # print(filedata)
            print(request.files['file'])
            print(request.files['file'].filename)
            # 获取上传的文件
            file = request.files['file']
            print(file.filename)

            # 获取上传的文件名
            original_filename = file.filename
            if original_filename == '':
                raise ValueError('No selected file')

            # 保存文件到上传目录
            original_file_path = os.path.join(UPLOAD_FOLDER, original_filename)
            print(original_file_path)
            file.save(original_file_path)

            # 获取文件的后缀名
            file_ext = os.path.splitext(original_filename)[1]
            print(file_ext)

            # 生成复制文件的新名字，带上时间戳
            timestamp = datetime.now().strftime('%Y%m%d%H%M%S')
            copied_filename = f"{os.path.splitext(original_filename)[0]}_{timestamp}{file_ext}"
            copied_file_path = os.path.join(UPLOAD_COPY_FOLDER, copied_filename)

            # 复制文件到新的目录
            shutil.copy(original_file_path, copied_file_path)

            # 使用 url_for 动态生成下载 URL
            # 请确保 FileDownload 类的定义和路由设置正确，并且 Flask-RESTx 正确注册了它。
            # 重要参数说明：
            # url_for('ns_alarm_rpt.filedownload', filename=copied_filename, _external=True)：
            # 'ns_alarm_rpt.filedownload'：这是指向文件下载的路由，你需要确保这个名称和你定义的下载
            # API
            # 路由匹配。
            # filename = copied_filename：这是传递给路由的动态参数（在这里是文件名）。
            # _external = True：这个参数告诉
            # url_for
            # 生成完整的绝对
            # URL，而不仅仅是相对路径。如果你只需要相对路径，可以将
            # _external
            # 去掉。
            # 结果：
            # 使用
            # url_for
            # 后，生成的
            # download_url
            # 会是类似
            # http: // yourdomain.com / ns_alarm_rpt / download / < filename > 这样的完整
            # URL，这样你不需要手动硬编码路径，URL
            # 会自动根据当前请求的域名和路由配置生成。
            #
            # 下载
            # API
            # 保持不变：
            # 你的下载
            # API
            # 路由 / download / < filename > 不需要改变，url_for
            # 会自动识别并匹配。
            # download_url = url_for('ns_alarm_rpt.filedownload', filename=copied_filename, _external=True)
            download_url = url_for('ns_alarm_rpt_file_download', filename=copied_filename, _external=True)
            # 构建文件下载的 URL
            # download_url = f"/ns_alarm_rpt/download/{copied_filename}"

            logging.info(f"文件上传并复制成功，原文件路径：{original_file_path}，复制文件路径：{copied_file_path}")

            # 构建响应数据
            response_data = {
                "message": {
                    "result": 1,  # 上传成功
                    "result_text": "文件上传并复制成功",
                    "data": [
                        {
                            'original_file_path': original_file_path,
                            'copied_file_path': copied_file_path,
                            'download_url': download_url
                        }
                    ]
                }
            }

            return response_data, 200

        except Exception as e:
            # 异常处理
            response_data = {
                "message": {
                    "result": 0,  # 上传失败
                    "result_text": f"文件上传失败: {str(e)}",
                    "data": []
                }
            }
            return response_data, 500


# 文件下载 API
@ns_alarm_rpt.route('/download/<filename>')
class FileDownload(Resource):
    def get(self, filename):
        """从复制目录中下载文件"""
        try:
            # 确保文件存在于复制目录中
            # file_path = os.path.join(UPLOAD_COPY_FOLDER, filename)
            file_path = os.path.join(UPLOAD_COPY_FOLDER, filename)
            print(file_path)
            print(os.curdir)
            print(os.path.exists(file_path))
            if not os.path.exists(file_path):
                response_data = {
                    "message": {
                        "result": 0,
                        "result_text": "文件未找到",
                        "data": []
                    }
                }
                return response_data, 404

            # send找不到UPLOAD_COPY_FOLDER 路径，原因不详，需要切换成 DOWNLOAD_FOLDER
            file_path_download = os.path.join(DOWNLOAD_FOLDER, filename)

            # 发送文件作为下载
            # return send_from_directory(UPLOAD_COPY_FOLDER, filename, as_attachment=True)
            # return send_from_directory(UPLOAD_COPY_FOLDER, filename, as_attachment=True)
            # rt = send_from_directory(UPLOAD_COPY_FOLDER, filename, as_attachment=True)
            # 貌似会扫整个目录，是个问题！
            basename = os.path.basename(filename)
            print(basename)
            # rt = send_file(safe_join(DOWNLOAD_FOLDER, secure_filename(filename)), as_attachment=True)
            rt = send_file(str(file_path_download))
            # rt = send_file(safe_join(DOWNLOAD_FOLDER, secure_filename(filename)))
            # 解决不支持中文的问题
            rt.headers["Content-Disposition"] = \
                "attachment;" \
                "filename*=UTF-8''{utf_filename}".format(
                    utf_filename=quote(basename.encode('utf-8'))
                )
            return rt

        except Exception as e:
            # 捕获异常并返回错误信息
            response_data = {
                "message": {
                    "result": 0,  # 下载失败
                    "result_text": f"文件下载过程中发生错误: {str(e)}",
                    "data": []
                }
            }
            return response_data, 500


#查询文件列表
@ns_alarm_rpt.route('/filelist')
class AlarmRptListResource(Resource):
    @ns_alarm_rpt.expect(alarm_file_list_query_model)
    @ns_alarm_rpt.marshal_with(response_alarm_file_list_model)
    def post(self):
        try:
            # 获取请求的目录名称
            dir_name = api.payload.get('dir_name')

            # 拼接绝对路径
            base_dir = os.path.abspath(dir_name)
            print(dir_name)
            if not os.path.exists(base_dir) or not os.path.isdir(base_dir):
                raise ValueError(f"指定目录 {dir_name} 不存在")

            # 查询目录下的所有文件
            file_list = []
            for file_name in os.listdir(base_dir):
                file_path = os.path.join(base_dir, file_name)

                # 确保只处理文件（忽略子目录）
                if os.path.isfile(file_path):
                    # 获取文件的详细信息
                    file_ctime = datetime.fromtimestamp(os.path.getctime(file_path)).strftime('%Y-%m-%d %H:%M:%S')
                    file_size = f"{os.path.getsize(file_path)} bytes"
                    file_type = os.path.splitext(file_name)[1][1:] if '.' in file_name else "Unknown"

                    # 构建单个文件的信息
                    file_list.append({
                        'file_name': file_name,
                        'file_ctime': file_ctime,
                        'file_type': file_type,
                        'file_size': file_size
                    })

            # 构建返回数据
            response_data = {
                "message": {
                    "result": 1,  # 查询成功
                    "result_text": "文件列表查询成功",
                    "count": len(file_list),
                    "data": file_list
                }
            }
            return response_data, 200


        except Exception as e:
            # 异常处理
            response_data = {
                "message": {
                    "result": 0,  # 上传失败
                    "result_text": f"文件查询失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 500


#######################################################################

# def process_alarm_file(dir_name, file_name):
#     file_path = os.path.join(dir_name, file_name)
#
#     # 1. 读取Excel文件，从第三行开始
#     df = pd.read_excel(file_path, header=2)
#
#     # 2. 填入模型Excel_alarm_model并处理数据
#     processed_data = []
#     for _, row in df.iterrows():
#         alarm_data = {
#             "team": row.get("team", ""),
#             "sys_name": row.get("sys_name", ""),
#             "dev_addr": row.get("dev_addr", ""),
#             "level": row.get("level", ""),
#             "occurrence_time": parse_datetime(row.get("occurrence_time")),
#             "info": row.get("info", ""),
#             "occurrence_date": row.get("occurrence_date", ""),
#             "cause": "",
#             "link_warning": row.get("link_warning", ""),
#             "link_change": row.get("link_change", ""),
#             "link_error": row.get("link_error", "")
#         }
#
#         # 3. 根据条件查询数据库
#         db_record = query_database(alarm_data)
#
#         # 4. 如果匹配,填入cause
#         if db_record:
#             alarm_data["cause"] = db_record.actual_cause
#
#         processed_data.append(alarm_data)
#
#     # 5. 更新Excel
#     update_excel(file_path, processed_data)
#
#     return {"message": {"result": 1, "result_text": "文件处理成功", "count": len(processed_data), "data": []}}
#
#
# def parse_datetime(dt_string):
#     if pd.isna(dt_string) or dt_string == '':
#         return None
#     try:
#         return datetime.strptime(str(dt_string), "%Y-%m-%d %H:%M:%S")
#     except ValueError:
#         try:
#             return pd.to_datetime(dt_string).strftime("%Y-%m-%d %H:%M:%S")
#         except:
#             return None
#
#
# def query_database(alarm_data):
#     filters = [
#         Zbfwb_kms_alarm.sys_name == alarm_data["sys_name"],
#         Zbfwb_kms_alarm.dev_addr == alarm_data["dev_addr"],
#         Zbfwb_kms_alarm.level == alarm_data["level"]
#     ]
#     if alarm_data["occurrence_time"]:
#         filters.append(Zbfwb_kms_alarm.occurrence_time == alarm_data["occurrence_time"])
#
#     return Zbfwb_kms_alarm.query.filter(and_(*filters)).first()
#
#
# def update_excel(file_path, processed_data):
#     # 读取原始Excel文件，保留前两行
#     original_df = pd.read_excel(file_path, nrows=2)
#
#     # 创建新的DataFrame，包含处理后的数据
#     new_df = pd.DataFrame(processed_data)
#
#     # 将原始的前两行与新数据合并
#     final_df = pd.concat([original_df, new_df], ignore_index=True)
#
#     # 将合并后的数据写回Excel文件
#     final_df.to_excel(file_path, index=False)

# 设置日志
logging.basicConfig(level=logging.DEBUG)
logger = logging.getLogger(__name__)


def process_alarm_file(dir_name, file_name):
    file_path = os.path.join(dir_name, file_name)

    # 1. 读取Excel文件，从第三行开始，指定 sheet 名称
    try:
        df = pd.read_excel(file_path, sheet_name='监控告警明细表_上海分中心', header=1)
        logger.info(f"Successfully read Excel file: {file_path}, sheet: 监控告警明细表_上海分中心")
    except Exception as e:
        logger.error(f"Error reading Excel file: {file_path}, sheet: 监控告警明细表_上海分中心. Error: {str(e)}")
        raise

    # 2. 填入模型Excel_alarm_model并处理数据
    processed_data = []
    for index, row in df.iterrows():
        # debug
        # print(row)
        print("--------------------------------------------------------------------------------------")
        print(df.iat[index, 0])
        print("++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
        # print(index)

        alarm_data = {
            "团队": df.iat[index, 0],
            "系统名称": df.iat[index, 1],
            "系统地址": df.iat[index, 2],
            "级别": df.iat[index, 3],
            "首次发生时间": parse_datetime(df.iat[index, 4]),
            "告警描述": df.iat[index, 5],
            "日期": df.iat[index, 6],
            "告警主要原因": "",
            "是否变更关联告警": df.iat[index, 8],
            "关联变更单号": df.iat[index, 9],
            "是否故障关联告警": df.iat[index, 10]
        }

        # debug
        print(alarm_data)

        # 3. 根据条件查询数据库
        try:
            db_record = query_database(alarm_data)

            # 4. 如果匹配,填入cause
            if db_record:
                alarm_data["告警主要原因"] = db_record.actual_cause
            else:
                logger.warning(f"No matching record found for row {index + 3}")
        except Exception as e:
            logger.error(f"Error querying database for row {index + 3}: {str(e)}")

        processed_data.append(alarm_data)

    # debug
    # print(processed_data)

    # 5. 更新Excel
    update_excel(file_path, processed_data)

    return {"message": {"result": 1, "result_text": "文件处理成功", "count": len(processed_data), "data": []}}


def parse_datetime(dt_string):
    if pd.isna(dt_string) or dt_string == '' or not dt_string:
        return None
    if isinstance(dt_string, datetime):
        return dt_string
    try:
        return pd.to_datetime(dt_string, errors='coerce')
    except Exception as e:
        logger.error(f"Error parsing datetime: {dt_string}. Error: {str(e)}")
        return None


def query_database(alarm_data):
    try:
        filters = []
        # 因系统名称差异过大，不纳入该筛选条件
        # if alarm_data["系统名称"]:
        #     filters.append(Zbfwb_kms_alarm.sys_name.like(f"%{alarm_data['系统名称']}%"))
        if alarm_data["系统地址"]:
            filters.append(Zbfwb_kms_alarm.dev_addr == alarm_data["系统地址"])
        if alarm_data["级别"]:
            level_map = {
                '一级': 'CRITICAL',
                '二级': 'MAJOR',
                '三级': 'MINOR',
                '四级': 'WARNING',
                '五级': 'INFO'
            }
            converted_level = level_map.get(alarm_data["级别"].upper(), alarm_data["级别"])
            filters.append(Zbfwb_kms_alarm.level == converted_level)
        if alarm_data["首次发生时间"]:
            # 使用 or_ 来处理可能的日期时间格式差异
            filters.append(or_(
                Zbfwb_kms_alarm.occurrence_time == alarm_data["首次发生时间"],
                Zbfwb_kms_alarm.occurrence_time == alarm_data["首次发生时间"].strftime("%Y-%m-%d %H:%M:%S")
            ))

        # debug
        logger.debug(f"系统名称: {alarm_data['系统名称']}")
        logger.debug(f"系统地址: {alarm_data['系统地址']}")
        logger.debug(f"级别: {alarm_data['级别']} (转换后: {converted_level if '级别' in alarm_data else 'N/A'})")
        logger.debug(f"首次发生时间: {alarm_data['首次发生时间']}")

        if not filters:
            logger.warning("No valid filters for database query")
            return None

        query = Zbfwb_kms_alarm.query.filter(and_(*filters))
        # logger.debug(f"SQL Query: {query}")
        logger.info(f"Get {alarm_data['团队']}")
        return query.first()
    except Exception as e:
        logger.error(f"Error in database query: {str(e)}")
        return None


def update_excel(file_path, processed_data):
    try:
        # 读取原始Excel文件
        with pd.ExcelFile(file_path) as xls:
            # 读取指定的sheet，保留前两行
            df_original = pd.read_excel(xls, sheet_name='监控告警明细表_上海分中心', nrows=0)

            # 读取其他sheet
            other_sheets = {sheet: pd.read_excel(xls, sheet_name=sheet) for sheet in xls.sheet_names if
                            sheet != '监控告警明细表_上海分中心'}

        # 创建新的DataFrame，包含处理后的数据
        new_df = pd.DataFrame(processed_data)

        print(df_original)

        # 将原始的前两行与新数据合并
        # final_df = pd.concat([df_original, new_df], axis=0, ignore_index=True)

        final_df = new_df

        # 创建ExcelWriter对象
        with pd.ExcelWriter(file_path, engine='openpyxl') as writer:
            # 写入更新后的sheet
            final_df.to_excel(writer, sheet_name='监控告警明细表_上海分中心', index=False)

            # 写入其他sheet
            for sheet_name, df in other_sheets.items():
                df.to_excel(writer, sheet_name=sheet_name, index=False)

        logger.info(f"Successfully updated Excel file: {file_path}")
    except Exception as e:
        logger.error(f"Error updating Excel file: {str(e)}")
        raise


# 填写告警原因
@ns_alarm_rpt.route('/filefill')
class AlarmRptFillResource(Resource):
    @ns_alarm_rpt.expect(alarm_file_fill_process_model)
    @ns_alarm_rpt.marshal_with(response_alarm_file_fill_model)
    def post(self):
        try:
            # 获取请求的目录名称
            dir_name = api.payload.get('dir_name')
            file_name = api.payload.get('file_name')

            #1  循环读取excel
            #2  填入模型  Excel_alarm_model
            #3  根据 "sys_name" "dev_addr" "level" "occurrence_time",读取 数据库
            #4  如果匹配 填入  cause
            #5 再更新 excel

            result = process_alarm_file(dir_name, file_name)
            return result, 200


        except Exception as e:
            # 异常处理
            response_data = {
                "message": {
                    "result": 0,  # 上传失败
                    "result_text": f"文件处理失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 500


# 删除指定文件
@ns_alarm_rpt.route('/filedelete')
class AlarmRptDeleteResource(Resource):
    @ns_alarm_rpt.expect(alarm_file_fill_process_model)
    @ns_alarm_rpt.marshal_with(response_alarm_file_fill_model)
    def post(self):
        try:
            # 获取请求的目录名称
            dir_name = api.payload.get('dir_name')
            file_name = api.payload.get('file_name')

            # 检查目录和文件是否存在
            if not dir_name or not file_name:
                raise ValueError("目录名或文件名为空")

            file_path = os.path.join(dir_name, file_name)

            if not os.path.exists(file_path):
                raise FileNotFoundError(f"文件 {file_path} 不存在")

            # 删除指定文件
            os.remove(file_path)

            # 构建成功的响应数据
            response_data = {
                "message": {
                    "result": 1,  # 删除成功
                    "result_text": f"文件 {file_name} 删除成功",
                    "count": 1,
                    "data": [file_name]
                }
            }
            return response_data, 200

        except FileNotFoundError as fnf_error:
            response_data = {
                "message": {
                    "result": 0,  # 删除失败
                    "result_text": f"文件未找到: {str(fnf_error)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 404

        except ValueError as val_error:
            response_data = {
                "message": {
                    "result": 0,  # 删除失败
                    "result_text": f"无效输入: {str(val_error)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 400

        except Exception as e:
            # 其他异常处理
            response_data = {
                "message": {
                    "result": 0,  # 删除失败
                    "result_text": f"文件处理失败: {str(e)}",
                    "count": 0,
                    "data": []
                }
            }
            return response_data, 500
